import time
from sqlalchemy.ext.declarative import declared_attr
from emmysql.em_sqlmanager import engine, metaData
from sqlalchemy.schema import Table

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert
from sqlalchemy.sql import select

@compiles(Insert)
def append(insert, compiler, **kw):
    s = compiler.visit_insert(insert, **kw)
    if ('append_dup_item' in insert.kwargs) and (isinstance(insert.kwargs['append_dup_item'], list)):
        m = [field+'=%('+field+')s' for field in insert.kwargs['append_dup_item']]
        # m = [field + '=CONCAT(' +field+',%('+field+')s)' for field in insert.kwargs['append_dup_item']]
        return s + " ON DUPLICATE KEY UPDATE " + ','.join(m)
    if ('append_dup_list' in insert.kwargs) and (isinstance(insert.kwargs['append_dup_list'], list)):
        # field+'=VALUES('+field+')'
        m = ['%(field)s=VALUES(%(field)s)'%{'field':field} for field in insert.kwargs['append_dup_list']]
        return s + " ON DUPLICATE KEY UPDATE " + ','.join(m)
    return s

class SQModel(object):

    # 这个放的是实体的字段名
    _fields = []
    _db_mapper = {}
    _table = None
    def __init__(self, *args, **kwargs):
        if len(args) > len(self._fields):
            raise TypeError('Expected {} arguments'.format(len(self._fields)))
        # set the arguments
        for name, value in zip(self._fields, args):
            setattr(self, name, value)
        for name in self._fields[len(args):]:
            setattr(self, name, kwargs.pop(name))
        extra_args = kwargs.keys() - self._fields
        for name in extra_args:
            setattr(self, name, kwargs.pop(name))
        if kwargs:
            raise TypeError('Invided arguments : {}'.format(','.join(kwargs)))

    def set_up_with_db_mapper(self, **kwargs):
        [setattr(self, self._db_mapper[field_name], kwargs[field_name]) for field_name, value in kwargs.items() if field_name in self._db_mapper.keys()]
        pass

    @classmethod
    def loadTable(cls, tbName, **kwargs):
        # _table = Table(tbName, metaData, autoload=True)
        if cls._table:
            if kwargs.keys():
                # 将数据库字段映射到实体，因为实体里面的名字可能跟数据库的不一样,key是对应属性的名字, value是本地数据库的字段
                cls._fields = [kwargs[x] for x in cls.columns() if x in kwargs.items()]
                # cls._fields = [x for x in kwargs.keys()]
            else:
                cls._fields = cls.columns()

    @declared_attr
    def __tablename__(cls):
        return cls.__name__.upper()

    @classmethod
    def columns(cls):
        return [c.name for c in cls._table.columns]

    @classmethod
    def sys_table(cls):
        pass

    def to_dict(self):
        if self._db_mapper:
            return dict([(c, getattr(self, c)) for c in self.__class__.columns() if c in self._db_mapper.values()])
            # return dict([(c, getattr(self, c)) for c in self._db_mapper.keys()])
        return dict([(c, getattr(self, c)) for c in self.__class__.columns()])

    @classmethod
    def insert(cls, self, **kwargs):
        i = self._table.insert(**kwargs).values(self.to_dict())
        engine.execute(i)

    @classmethod
    def insert_list(cls, data_list, **kwargs):
        if isinstance(data_list, list):
            connect = engine.connect()
            connect.execute(cls._table.insert(**kwargs), [item.to_dict() for item in data_list if isinstance(item, SQModel)])

    @classmethod
    def select_all(cls):
        connect = engine.connect()
        rp = connect.execute(select([cls._table]))
        return rp.fetchall()



        # @classmethod
    # def insert_list_tranction(cls, data_list, **kwargs):
    #     if isinstance(data_list, list):
    #         connect = engine.connect()
    #         connect.execution_options(autocommit=False)
    #         trans = connect.begin()
    #         try:
    #             for item in data_list :
    #                 if isinstance(item, SQModel):
    #                     i = cls._table.insert().values(item.to_dict())
    #                     connect.execute(i)
    #         except Exception as error:
    #             trans.rollback()
    #         else:
    #             trans.commit()


class BaseModel(SQModel):
    pass





